package com.yingdong.db;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.yingdong.annotation.AutoIncrease;
import com.yingdong.annotation.PrimeKey;

public abstract class DBObject {

	public static final int DBSTATUS_INSERT = 0;

	public static final int DBSTATUS_UPDATE = 1;

	public static final int DBSTATUS_UPDATED = 2;

	public static final int DBSTATUS_DELETE = 3;

	public static final int DBSTATUS_DELETED = 4;

	static Log logger = LogFactory.getLog(DBObject.class);

	private int dbStatus = 0;

	public abstract String getSelectByKeyString();

	public abstract String getUpdateByKeyString();

	public abstract String getTableName();

	public abstract String getSelectAllObjectsString();

	public abstract String getSelectAllObjectsKeyString();

	public abstract String getDelObjectKeyString();

	public int getDbStatus() {
		return dbStatus;
	}

	public void setDbStatus(int dbStatus) {
		this.dbStatus = dbStatus;
	}

	public int selectCount() {
		String sql = "SELECT COUNT(*) FROM " + getTableName();
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = DBManager.getConnection();
			st = connection.prepareStatement(sql);
			rs = st.executeQuery();
			int result = 0;
			while (rs.next()) {
				result = rs.getInt(1);
			}
			return result;
		} catch (Exception e) {
			logger.error("", e);

			return -1;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				DBManager.closeConnection(connection);
			}
		}
	}

	public void saveBatch(Collection<? extends DBObject> collection) {
		PreparedStatement updateST = null;
		Statement insertST = null;
		PreparedStatement delST = null;
		Connection connection = null;
		try {
			String delSql = this.getDelObjectKeyString();
			String updateSql = this.getUpdateByKeyString();
			connection = DBManager.getConnection();
			Field delField = this.getClass().getDeclaredField("delList");
			delField.setAccessible(true);
			List<? extends DBObject> delList = (List<? extends DBObject>) delField.get(null);
			Field[] fields = this.getClass().getDeclaredFields();
			connection.setAutoCommit(false);
			updateST = connection.prepareStatement(updateSql);
			insertST = connection.createStatement();
			delST = connection.prepareStatement(delSql);
			boolean executeUpdate = false, excuteInsert = false, excuteDel = false;
			for (DBObject delObject : delList) {
				switch (delObject.getDbStatus()) {
				case DBSTATUS_DELETE:
					int j = 0;
					for (Field field : fields) {
						if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
							continue;
						if (field.getAnnotation(PrimeKey.class) != null) {
							field.setAccessible(true);
							Object v = field.get(delObject);
							if (v != null) {
								delST.setObject(++j, v);
							}
						}
					}
					delST.addBatch();
					excuteDel = true;
					break;
				default:
					break;
				}
			}
			for (DBObject obj : collection) {
				switch (obj.getDbStatus()) {
				case DBSTATUS_UPDATED:
					break;
				case DBSTATUS_INSERT:
					String sql = "INSERT INTO " + obj.getTableName();
					sql += " (";
					String sql1 = "VALUES(";
					int j = 0;
					for (int i = 0; i < fields.length; i++) {
						Field field = fields[i];
						if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
							continue;
						field.setAccessible(true);
						Object value = field.get(obj);
						if (value != null) {
							sql += "`" + field.getName() + "`,";
							if (value instanceof String)
								sql1 += "'" + value + "',";
							else
								sql1 += "" + value + ",";
						}
					}
					sql = sql.substring(0, sql.length() - 1);
					sql1 = sql1.substring(0, sql1.length() - 1);
					sql += ")";
					sql1 += ")";
					sql += sql1;

					insertST.addBatch(sql);
					excuteInsert = true;
					break;
				case DBSTATUS_UPDATE:
					j = 0;
					for (Field field : fields) {
						if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
							continue;
						if (field.getAnnotation(PrimeKey.class) == null) {
							field.setAccessible(true);
							Object v = field.get(obj);
							if (v != null) {
								updateST.setObject(++j, v);
							}
						}
					}
					for (Field field : fields) {
						if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
							continue;
						if (field.getAnnotation(PrimeKey.class) != null) {
							field.setAccessible(true);
							Object v = field.get(obj);
							if (v != null) {
								updateST.setObject(++j, v);
							}
						}
					}
					updateST.addBatch();
					executeUpdate = true;
					break;
				case DBSTATUS_DELETE:

					break;
				case DBSTATUS_DELETED:
					break;
				default:
					break;
				}

			}
			if (executeUpdate)
				updateST.executeBatch();
			if (excuteInsert)
				insertST.executeBatch();
			if (excuteDel)
				delST.executeBatch();
			connection.commit();
			Class<?> autoType = null;
			Field autoIncreaseFiled = null;
			for (Field field : fields) {
				if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
					continue;
				if (field.getAnnotation(AutoIncrease.class) != null) {
					autoType = field.getType();
					autoIncreaseFiled = field;
					break;

				}
			}
			ResultSet keys = null;
			if (autoType != null && executeUpdate) {
				keys = insertST.getGeneratedKeys();
			}

			for (DBObject obj : collection) {
				switch (obj.getDbStatus()) {
				case DBSTATUS_INSERT:
					if (keys != null) {
						keys.next();
						Object valueObject = null;
						if (autoType == Integer.class) {
							valueObject = keys.getInt(1);
						} else if (autoType == Long.class) {
							valueObject = keys.getLong(1);
						} else if (autoType == String.class) {
							valueObject = keys.getString(1);
						} else if (autoType == Float.class) {
							valueObject = keys.getFloat(1);
						} else if (autoType == Double.class) {
							valueObject = keys.getDouble(1);
						}
						autoIncreaseFiled.set(obj, valueObject);
					}
				case DBSTATUS_UPDATE:
					obj.setDbStatus(DBSTATUS_UPDATED);
				case DBSTATUS_DELETE:
					obj.setDbStatus(DBSTATUS_DELETED);
				default:
					break;
				}
			}
			delList.clear();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (updateST != null) {
					updateST.clearBatch();
					updateST.close();
				}
				if (insertST != null) {
					insertST.clearBatch();
					insertST.close();
				}
				if (delST != null) {
					delST.clearBatch();
					delST.close();
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				DBManager.closeConnection(connection);
			}
		}

	}

	public <T> T selectAllObjectsByKey(Object... args) {
		T result = null;
		Class<? extends DBObject> clz = this.getClass();
		String sql = getSelectAllObjectsKeyString();
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = DBManager.getConnection();
			st = connection.prepareStatement(sql);
			int j = 0;
			for (Object obj : args) {
				st.setObject(++j, obj);
			}
			rs = st.executeQuery();
			ResultSetMetaData metadata = rs.getMetaData();
			int count = metadata.getColumnCount();
			int rowCount = 0;
			while (rs.next()) {
				DBObject obj = clz.newInstance();
				obj.setDbStatus(DBSTATUS_UPDATED);
				result = ((T) obj);
				for (int i = 0; i < count; i++) {
					Object valueObject = rs.getObject(i + 1);
					String columName = metadata.getColumnName(i + 1);
					Field field = obj.getClass().getDeclaredField(columName);
					if (field != null) {
						field.setAccessible(true);
						field.set(obj, valueObject);
					}
				}
				rowCount++;
			}
			if (rowCount > 1)
				throw new Exception("根据主键查询结果大于1 表:" + this.getClass().getName());
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);

		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				DBManager.closeConnection(connection);
			}
		}

	}

	public <T> List<T> selectObjectsByCondition(Object... args) {
		List<T> result = new ArrayList<>();
		Class<? extends DBObject> clz = this.getClass();
		String sql = getSelectAllObjectsString();
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			int j = 0;
			if (args.length % 2 != 0) {
				throw new Exception("参数传入个数不对，应该为偶数个");
			}
			sql += "WHERE 1=1";
			for (int i = 0; i < args.length / 2; i++) {
				sql += " AND " + args[i * 2] + "=?";
			}
			connection = DBManager.getConnection();
			st = connection.prepareStatement(sql);
			for (int i = 0; i < args.length / 2; i++) {
				st.setObject(++j, args[i * 2 + 1]);
			}
			rs = st.executeQuery();
			ResultSetMetaData metadata = rs.getMetaData();
			int count = metadata.getColumnCount();
			while (rs.next()) {
				DBObject obj = clz.newInstance();
				obj.setDbStatus(DBSTATUS_UPDATED);
				result.add((T) obj);
				for (int i = 0; i < count; i++) {
					Object valueObject = rs.getObject(i + 1);
					String columName = metadata.getColumnName(i + 1);
					Field field = obj.getClass().getDeclaredField(columName);
					if (field != null) {
						field.setAccessible(true);
						field.set(obj, valueObject);
					}
				}

			}
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				DBManager.closeConnection(connection);
			}
		}

	}

	public <T> List<T> selectAllObjects() {
		List<T> result = new ArrayList<>();
		Class<? extends DBObject> clz = this.getClass();
		String sql = getSelectAllObjectsString();
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = DBManager.getConnection();
			st = connection.prepareStatement(sql);
			rs = st.executeQuery();
			ResultSetMetaData metadata = rs.getMetaData();
			int count = metadata.getColumnCount();
			while (rs.next()) {
				DBObject obj = clz.newInstance();
				obj.setDbStatus(DBSTATUS_UPDATED);
				result.add((T) obj);
				for (int i = 0; i < count; i++) {
					Object valueObject = rs.getObject(i + 1);
					String columName = metadata.getColumnName(i + 1);
					Field field = obj.getClass().getDeclaredField(columName);
					if (field != null) {
						field.setAccessible(true);
						field.set(obj, valueObject);
					}
				}

			}
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				DBManager.closeConnection(connection);
			}
		}

	}

	public void changeDBStatus() {
		if (dbStatus == DBSTATUS_UPDATED) {
			dbStatus = DBSTATUS_UPDATE;
		}
	}

	public void delete() {
		if (dbStatus == DBSTATUS_INSERT) {
			dbStatus = DBSTATUS_DELETED;
		} else if (dbStatus == DBSTATUS_UPDATE || dbStatus == DBSTATUS_UPDATED) {
			dbStatus = DBSTATUS_DELETE;
			Field delField;
			try {
				delField = this.getClass().getDeclaredField("delList");
				delField.setAccessible(true);
				List delList = (List) delField.get(null);
				if (!delList.contains(this)) {
					delList.add(this);
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}

		}
	}

	public Object saveToDB() {
		PreparedStatement st = null;
		Connection connection = null;
		try {

			Field[] fields = this.getClass().getDeclaredFields();
			connection = DBManager.getConnection();
			int j = 0;
			switch (this.getDbStatus()) {
			case DBSTATUS_DELETE:
				st = connection.prepareStatement(getDelObjectKeyString());
				for (Field field : fields) {
					if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
						continue;
					if (field.getAnnotation(PrimeKey.class) != null) {
						field.setAccessible(true);
						Object v = field.get(this);
						if (v != null) {
							st.setObject(++j, v);
						}
					}
				}
				return st.executeUpdate();

			case DBSTATUS_INSERT:
				String sql = "INSERT INTO " + this.getTableName();
				sql += " (";
				String sql1 = "VALUES(";

				for (int i = 0; i < fields.length; i++) {
					Field field = fields[i];
					if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
						continue;
					field.setAccessible(true);
					Object value = field.get(this);
					if (value != null) {
						sql += "`" + field.getName() + "`,";
						if (value instanceof String)
							sql1 += "'" + value + "',";
						else
							sql1 += "" + value + ",";
					}
				}
				sql = sql.substring(0, sql.length() - 1);
				sql1 = sql1.substring(0, sql1.length() - 1);
				sql += ")";
				sql1 += ")";
				sql += sql1;
				st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				st.executeUpdate();
				ResultSet rs = st.getGeneratedKeys();
				Object index = null;
				if (rs.next()) {
					index = rs.getInt(1);

				}
				rs.close();
				return index;
			case DBSTATUS_UPDATE:
				String updateSql = this.getUpdateByKeyString();
				st = connection.prepareStatement(updateSql);
				for (Field field : fields) {
					if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
						continue;
					if (field.getAnnotation(PrimeKey.class) == null) {
						field.setAccessible(true);
						Object v = field.get(this);
						if (v != null) {
							st.setObject(++j, v);
						}
					}
				}
				for (Field field : fields) {
					if ((field.getModifiers() & java.lang.reflect.Modifier.STATIC) == Modifier.STATIC)
						continue;
					if (field.getAnnotation(PrimeKey.class) != null) {
						field.setAccessible(true);
						Object v = field.get(this);
						if (v != null) {
							st.setObject(++j, v);
						}
					}
				}
				return st.executeUpdate();
			case DBSTATUS_UPDATED:
				return null;
			case DBSTATUS_DELETED:
				return null;

			default:
				return null;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error("", e);
			return null;
		} finally {
			try {
				st.close();

			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				try {
					DBManager.closeConnection(connection);
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
			}

		}
	}

}
